package com.jiudao.dao;

import org.apache.commons.lang.StringUtils;
import org.springframework.stereotype.Repository;

import com.google.gson.JsonArray;
import com.jiudao.entity.Applist;
import com.jiudao.entity.CustomerGroup;
import com.jiudao.entity.Empno;
import com.jiudao.entity.Riskcon;
import com.jiudao.entity.Risklist;

/**
 * 营销员分析dao层
 * 
 * @author gyj
 *
 */
@Repository
public class EmpnoAnalysisDao extends BaseDao {

	/**
	 * 入职后第一次举绩
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray firstDays(String branch, String beginDate, String endDate) {
		String sql = " select " + empno.column(Empno.FIRSTDAYS) + " ,count(1) COUNT_EMPNO from " + empno.tablename()
				+ " where " + empno.column(Empno.FIRSTDAYS) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.FIRSTDAYS) + " order by " + empno.column(Empno.FIRSTDAYS) + " asc ";

		logger.info(sql);
		String columns = Empno.FIRSTDAYS + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 入职后转正所需时间
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray regularDays(String branch, String beginDate, String endDate) {
		String sql = " select " + empno.column(Empno.REGULARDAYS) + " ,count(1) COUNT_EMPNO from " + empno.tablename()
				+ " where " + empno.column(Empno.REGULARDAYS) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.REGULARDAYS) + " order by " + empno.column(Empno.REGULARDAYS)
				+ " asc ";

		logger.info(sql);
		String columns = Empno.REGULARDAYS + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 转正后第一次晋升所需时间
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray promotionDays(String branch, String beginDate, String endDate) {
		String sql = " select " + empno.column(Empno.PROMOTIONDAYS) + " ,count(1) COUNT_EMPNO from " + empno.tablename()
				+ " where " + empno.column(Empno.PROMOTIONDAYS) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.PROMOTIONDAYS) + " order by " + empno.column(Empno.PROMOTIONDAYS)
				+ " asc ";

		logger.info(sql);
		String columns = Empno.PROMOTIONDAYS + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 离职人员留存时间
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray stayDays(String branch, String beginDate, String endDate) {
		String sql = " select " + empno.column(Empno.STAYDAYS) + " ,count(1) COUNT_EMPNO from " + empno.tablename()
				+ " where " + empno.column(Empno.STAYDAYS) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.STAYDAYS) + " order by " + empno.column(Empno.STAYDAYS) + " asc ";

		logger.info(sql);
		String columns = Empno.STAYDAYS + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 入职后三月平均FYC
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray threeMonthFyc(String branch, String beginDate, String endDate, String service) {
		String sql = " select " + empno.column(Empno.THREE_MONTHS_FYC) + " ,count(1) COUNT_EMPNO from "
				+ empno.tablename();
		if (service.equals("onJob")) {
			sql += " where " + empno.column(Empno.STAYDAYS) + " =0 ";
		} else {
			sql += " where " + empno.column(Empno.STAYDAYS) + " !=0 ";
		}
		sql += " and " + empno.column(Empno.THREE_MONTHS_FYC) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.THREE_MONTHS_FYC) + " order by " + empno.column(Empno.THREE_MONTHS_FYC)
				+ " asc ";

		logger.info(sql);
		String columns = Empno.THREE_MONTHS_FYC + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

	/**
	 * 入职后六月平均FYC
	 * 
	 * @param branch
	 * @param beginDate
	 * @param endDate
	 * @return
	 */
	public JsonArray sixMonthFyc(String branch, String beginDate, String endDate, String service) {
		String sql = " select " + empno.column(Empno.SIX_MONTHS_FYC) + " ,count(1) COUNT_EMPNO from "
				+ empno.tablename();
		if (service.equals("onJob")) {
			sql += " where " + empno.column(Empno.STAYDAYS) + " =0 ";
		} else {
			sql += " where " + empno.column(Empno.STAYDAYS) + " !=0 ";
		}
		sql += " and " + empno.column(Empno.SIX_MONTHS_FYC) + " !=0 ";
		if (!StringUtils.isEmpty(branch)) {
			sql += " and " + empno.column(Empno.BRANCH) + " = '" + branch + "'";
		}
		if (!StringUtils.isEmpty(beginDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " >= '" + beginDate + "'";
		}
		if (!StringUtils.isEmpty(endDate)) {
			sql += " and " + empno.column(Empno.INDATE) + " < '" + endDate + "'";
		}
		sql += " group by " + empno.column(Empno.SIX_MONTHS_FYC) + " order by " + empno.column(Empno.SIX_MONTHS_FYC)
				+ " asc ";

		logger.info(sql);
		String columns = Empno.SIX_MONTHS_FYC + ",COUNT_EMPNO";
		return executeQuery(sql, columns);
	}

}
